-- @owner: zou_jialiang050
-- @date: 2024/4/2
-- @testpoint: pipelined函数返回多列嵌套表类型
-- @modify: modified by zhangbingliang at 2025.01,优化用例适配代码变更
--step1:创建嵌套表类型;expect:成功
create or replace type type_0001 as (c1 integer, c2 tinyint, c3 numeric(10, 4), c4 decimal(12),
c5 number(10,4), c6 float(10), c7 bit(1), c8 dec(10,3), c9 boolean, c10 char(10),
c11 nchar(10), c12 varchar(200), c13 varchar2(20), c14 text, c15 blob, c16 date, c17 time, c18 interval);
create or replace type tb_type_0001 as table of type_0001;

--step2:创建管道函数并查看返回值;expect:成功
create or replace function func_pipelined_001(count in number)
returns tb_type_0001 pipelined
as $$
declare result type_0001;
begin
for i in 1 .. count loop
result.c1 = i + 1;
result.c2 = i + 1;
result.c3 = i + 0.005;
result.c4 = i - 0.5;
result.c5 = i + 0.7;
result.c6 = i * 0.5;
result.c7 = 1;
result.c8 = (i / 0.5);
result.c9 = true;
result.c10 = '1sadf1234';
result.c11 = 'sdfwdf';
result.c12 = '123wsdfwaerwefasd';
result.c13 = 'sdfwersdf';
result.c14 = 'exdgeww';
result.c15 = 'a'::blob;
result.c16 = date'2022-01-01';
result.c17 = time'11:11:11';
result.c18 = age(timestamp '2001-04-10', timestamp '1957-06-13');
pipe row(result);
end loop;
return;
end;
$$ language plpgsql;
/

--step3:调用pipelined函数并查看返回类型;expect:成功
select func_pipelined_001(3);
select * from func_pipelined_001(3);
select * from table(func_pipelined_001(3));
select c1 from table(func_pipelined_001(3));
select pg_typeof(func_pipelined_001(3));

--step4:使用alter修改函数名称;expect:成功
alter function func_pipelined_001(count in number) rename to func_pipelined_001_1;
select func_pipelined_001_1(3);
select * from func_pipelined_001_1(3);
select * from table(func_pipelined_001_1(3));

--step5:使用create table as创建表;expect:成功
drop table if exists t_pipelined_0001;
create table t_pipelined_0001 as select * from func_pipelined_001_1(1);
insert into t_pipelined_0001 (select * from table(func_pipelined_001_1(1)));

--step6:清理环境;expect:成功
drop type if exists tb_type_0001 cascade;
drop type if exists type_0001 cascade;
drop function if exists func_pipelined_001_1;
drop table if exists t_pipelined_0001;
